import pandas as pd


class DouyinSalesAnalysis:
    def __init__(self):
        self.data = None

    def load_data(self):
        """加载抖音数据文件"""
        # dtype:参数指定特定列的数据类型：

        dou1 = pd.read_excel(r'E:\每日\抖音1.xlsx',
                             dtype={'商品ID': str, '达人昵称': str, '选购商品': str})

        dou2 = pd.read_excel(r'E:\每日\抖音2.xlsx',
                             dtype={'商品ID': str, '达人昵称': str, '选购商品': str})

        dou3 = pd.read_excel(r'E:\每日\抖音3.xlsx',
                             dtype={'商品ID': str, '达人昵称': str, '选购商品': str})

        dou4 = pd.read_excel(r'E:\每日\抖音4.xlsx',
                             dtype={'商品ID': str, '达人昵称': str, '选购商品': str})



        return [dou1, dou2, dou3, dou4]

    def preprocess_data(self, data_list):
        """预处理数据：提取列、合并、清理"""
        processed_data = []
        platforms = ['抖音1', '抖音2', '抖音3', '抖音4']
        columns_to_extract = [
            '商品ID', '选购商品', '商家编码', '商品数量', '订单应付金额', '订单状态', '售后状态',
            '流量来源', '达人昵称', '广告渠道', '流量类型', '流量渠道', '流量体裁',
            '订单提交时间', '平台实际承担优惠金额', '达人实际承担优惠金额'
        ]

        for i, data in enumerate(data_list):
            df = data[columns_to_extract].copy()
            df['平台'] = platforms[i]
            processed_data.append(df)

        merged_data = pd.concat(processed_data)

        merged_data['订单应付金额加优惠'] = (
                merged_data['订单应付金额'] +
                merged_data['平台实际承担优惠金额'].fillna(0) +
                merged_data['达人实际承担优惠金额'].fillna(0)
        )

        return merged_data

    def transform_data_structure(self, data):
        """转换数据结构并重命名列"""
        result = data[[
            '商品ID', '选购商品', '商家编码', '商品数量', '订单应付金额加优惠',
            '订单状态', '售后状态', '订单应付金额加优惠',
            '流量来源', '达人昵称', '广告渠道', '流量类型', '流量渠道', '流量体裁',
            '订单提交时间', '平台'
        ]].copy()

        result.columns = [
            '商品ID', '选购商品', '商家编码', '商品数量', '订单应付金额',
            '订单状态', '售后状态', '退款金额', '流量来源', '达人昵称',
            '广告渠道', '流量类型', '流量渠道', '流量体裁',
            '订单提交时间', '平台'
        ]

        return result

    def clean_and_filter_data(self, data):
        """清理和过滤数据"""
        data['订单提交时间'] = pd.to_datetime(data['订单提交时间'])
        data['日期'] = data['订单提交时间'].dt.date

        valid_data = data[
            (data['订单应付金额'] >= 0.01) &
            (data['商家编码'] != '抖音直播间赠品')
            ].copy()

        valid_data['商品ID'] = valid_data['商品ID'].str.strip()
        valid_data['商家编码'] = valid_data['商家编码'].str.strip()

        return valid_data

    def classify_channel(self, row):
        """渠道分类逻辑"""
        product = str(row['选购商品'])
        influencer = str(row['达人昵称'])
        flow_source = str(row['流量来源'])
        ad_channel = str(row['广告渠道'])
        platform = str(row['平台'])

        if (influencer == '宛禾米线官方旗舰店' or
                'z1' in product.lower()):
            return '自播间1.0'

        elif (influencer in ['宛禾米线速食官方旗舰店', '宛禾米线',
                             '宛禾速食官方旗舰店', '宛禾食品', '宛禾速食直播间'] or
              'z3' in product.lower()):
            return '自播间3.0'

        elif 'wzm' in product.lower():
            return '窝子面直播间'

        elif (influencer == '宛禾食品旗舰店' or
              'z5' in product.lower()):
            return '自播间5.0'

        elif (influencer in ['宛禾速食旗舰店', '宛禾食品旗舰店甄选号'] or
              'z6' in product.lower()):
            return '自播间6.0'

        elif (influencer == '宛禾官方旗舰店' or
              'z8' in product.lower()):
            return '自播间8.0'

        elif (influencer == '宛禾米线速食旗舰店' or
              'lsf' in product.lower()):
            return '螺蛳土豆粉直播间'

        elif platform == '抖音1' and 'dsp' in product.lower():
            return '自播间1.0'

        elif platform == '抖音2' and 'dsp' in product.lower():
            return '自播间5.0'

        elif platform == '抖音3' and 'dsp' in product.lower():
            return '自播间8.0'

        elif platform == '抖音4' and 'dsp' in product.lower():
            return '螺蛳土豆粉直播间'

        elif pd.notna(row['达人昵称']) and row['达人昵称'] != 'nan':
            return '达人分发'

        elif flow_source == '精选联盟':
            return '达人分发'

        elif ad_channel == '商品卡':
            return '超级商品卡'

        else:
            return '自然单'

    def apply_channel_classification(self, data):
        """应用渠道分类到数据集"""
        data['渠道'] = data.apply(self.classify_channel, axis=1)
        return data

    def filter_shipped_orders(self, data):
        """筛选发货成功的订单"""
        valid_order_status = ['已发货', '部分发货', '待发货', '已完成', '已支付']

        invalid_after_sales = [
            '退款成功', '退款完成', '已全额退款',
            '(锁定)等待确认收货', '(删除)等待出库',
            '(删除)等待确认收货', '售后完成'
        ]

        shipped_orders = data[
            data['订单状态'].isin(valid_order_status) &
            ~data['售后状态'].isin(invalid_after_sales)
            ]

        return shipped_orders

    def filter_refund_orders(self, data):
        """筛选退款订单"""
        refund_statuses = ['退款成功', '退款完成', '已全额退款']

        refund_orders = data[data['售后状态'].isin(refund_statuses)]

        return refund_orders

    def analyze_sales_by_channel(self, shipped_data, refund_data):
        """按渠道分析销售数据"""
        # 发货订单汇总
        sales_summary = shipped_data.groupby(
            ['日期', '平台', '渠道'], as_index=False
        ).agg(
            销售额=('订单应付金额', 'sum'),
            单量=('商品数量', 'count')
        )

        # 退款订单汇总
        refund_summary = refund_data.groupby(
            ['日期', '平台', '渠道'], as_index=False
        ).agg(退款金额=('退款金额', 'sum'))

        # 合并销售和退款数据
        merged_result = pd.merge(
            sales_summary, refund_summary,
            on=['日期', '平台', '渠道'],
            how='left'
        )

        merged_result['退款金额'] = merged_result['退款金额'].fillna(0)
        merged_result['总销售额'] = merged_result['销售额'] + merged_result['退款金额']

        return merged_result

    def run_analysis(self):
        """运行完整的分析流程"""
        try:
            # 1. 加载数据
            data_list = self.load_data()

            # 2. 预处理数据
            processed_data = self.preprocess_data(data_list)

            # 3. 转换数据结构
            transformed_data = self.transform_data_structure(processed_data)

            # 4. 清理和过滤数据
            cleaned_data = self.clean_and_filter_data(transformed_data)

            # 5. 应用渠道分类
            classified_data = self.apply_channel_classification(cleaned_data)

            # 6. 筛选订单
            shipped_orders = self.filter_shipped_orders(classified_data)
            refund_orders = self.filter_refund_orders(classified_data)

            # 7. 分析销售数据
            result = self.analyze_sales_by_channel(shipped_orders, refund_orders)

            # 8. 保存结果
            output_path = r'E:\每日结果\2_抖音销量-不同渠道.xlsx'
            result.to_excel(output_path, index=False)

            return result

        except Exception as e:
            print(f"分析过程中出现错误: {str(e)}")
            import traceback
            traceback.print_exc()
            return None


def main():
    analyzer = DouyinSalesAnalysis()
    result = analyzer.run_analysis()
    return result


if __name__ == "__main__":
    main()
